package com.kaifamiao.jdbc.exception;

import java.sql.*;
import java.sql.Date;
import java.util.*;

public class ResultHandling {

    public static void main(String[] args) {

        /*
        String driverClassName = "com.mysql.cj.jdbc.Driver";
        String url = "jdbc:mysql://localhost:3306/kaifamiao?serverTimezone=Asia/Chongqing";
        String username = "kfm";
        String password = "kfm";
        */

        String driverClassName = "oracle.jdbc.OracleDriver";
        String url = "jdbc:oracle:thin:@localhost:1521:kaifamiao";
        String username = "kaifamiao";
        String password = "kaifamiao";

        try {
            Class.forName( driverClassName );
        }
        // 捕获 ClassNotFoundException
        catch (ClassNotFoundException cause) {
            // 抛出 自定义异常类 的实例
            throw new DataAccessException( "驱动加载失败" , cause);
        }

        Connection conn = null ;
        try {
            conn = DriverManager.getConnection(url, username, password);
        } catch ( SQLException cause ) {
            throw new DataAccessException( "连接失败" , cause);
        }

        PreparedStatement ps = null ;
        String sql = "SELECT empno, ename, hiredate, job, sal FROM emp WHERE deptno = ?";
        try {
            ps = conn.prepareStatement( sql );
        } catch (SQLException cause) {
            throw new DataAccessException( "创建PreparedStatement时发送错误" , cause);
        }

        try {
            ps.setInt( 1, 20 );
        } catch (SQLException cause) {
            throw new DataAccessException( "设置SQL占位符参数时出错" , cause);
        }

        ResultSet rs = null;
        try {
            rs = ps.executeQuery();
        } catch (SQLException cause) {
            DataAccessException dae = new DataAccessException( "查询失败" , cause);
            throw dae;
        }

        final List< Map<String,Object> > emps = new ArrayList<>();

        try {
            while (rs.next()) {
                Map<String,Object> map = new HashMap<>();
                int empno = rs.getInt("empno");
                map.put( "empno", empno );
                String name = rs.getString("ename");
                map.put( "ename", name );
                java.sql.Date hiredate = rs.getDate("hiredate");
                map.put( "hiredate", hiredate );
                String job = rs.getString("job");
                map.put( "job", job );
                double salary = rs.getDouble("sal");
                map.put( "sal", salary );
                emps.add( map );
            }
        } catch ( SQLException cause ) {
            throw new DataAccessException( "从结果集提取数据时发送错误" , cause);
        }

        try {
            rs.close();
        } catch (SQLException cause) {
            throw new DataAccessException( "关闭结果集时发送错误" , cause);
        } finally {
            try {
                ps.close();
            } catch (SQLException cause) {
                throw new DataAccessException( "关闭Statement时发送错误" , cause);
            } finally {
                try {
                    conn.close();
                } catch (SQLException cause) {
                    throw new DataAccessException( "关闭连接时发送错误" , cause);
                }
            }
        }

        Iterator< Map<String,Object> > itor = emps.iterator();
        while( itor.hasNext() ){
            Map<String,Object> map = itor.next();
            System.out.println( map );
        }

    }

}
